A database management system (DBMS) facilitates interaction with database data. The DBMS efficiently manages requests or queries from users and programs so that these entities are free from having to know specifics pertaining to how and where data is physically stored. Furthermore, in handling requests, the DBMS ensures the integrity and security with respect to the data. Although other systems are emerging, relational database management systems (RDBMS) remain the most common DBMS.
Design of such a database system is multifaceted task involving three fundamental phases, namely conceptual, logical and physical. First, a database designer or administrator needs to develop a conceptual model. This can be accomplished by identifying important entities as well as relationships amongst them (i.e., ER modeling). Once the entities and relationships are understood, the data can be arranged in a logical structure. This logical structure or schema is typically designed to map to storage objects of the DBMS such as tables in a RDBMS. More specifically, a database schema is discovered to support the conceptual model. The database schema can identify tables, their definitions, constraints, foreign keys and the like. In the physical phase, the logical schema is realized in a DBMS. Stated differently, the logical schema is mapped against provided database options including indexes, heaps, views, etc.
Conventionally, the performance of a database system depends crucially on its physical database design. In particular, physical design structures such as indexes, materialized views and the like are selectively employed to improve performance significantly. For example, consider a particular database query issued numerous times throughout a day. If a database view is materialized to capture the query answer, response time can be reduced substantially.
Although a database administrator (DBA) or designer is typically responsible for database design, reliance on automated tools to make good design decisions has become increasingly common and necessary, especially with large-scale deployment of databases. These automated tools drastically reduce a DBA's burden. For example, DBAs often employ modeling tools that facilitate generation of conceptual, logical and physical models. Furthermore, automated tools are also employed to tune a physical database design, which may be refined later, if necessary, and implemented by the DBA.